﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace _EF返回DateSet
{
    public class DataHelper
    {
        #region 获取DataSet返回结构集
        public DataSet GetDataSet(string sql, params SqlParameter[] para)
        {
            List<string> sql2 = new List<string>();
            for (int i = 0; i < para.Length; i++)
            {
                SqlParameter p = para[i];
                sql2.Add(p.ParameterName);
            }
            DataSet sd = new DataSet();
            using (var db = new BaseContext())
            {
                SqlConnection conn = db.Database.Connection as SqlConnection;
                using (SqlCommand cmd = new SqlCommand(sql + " " + string.Join(",", sql2.ToArray()), conn))
                {
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(sd);
                }
            }
            return sd;
        }
        #endregion

        #region 获取Table返回DataTable
        public DataTable GetDataTable(string sql, params SqlParameter[] para)
        {
            DataTable dt = new DataTable();
            List<string> sql2 = new List<string>();

            for (int i = 0; i < para.Length; i++)
            {
                SqlParameter p = para[i];
                sql2.Add(p.ParameterName);
            }

            using (var db = new BaseContext())
            {
                SqlConnection conn = db.Database.Connection as SqlConnection;
                using (SqlCommand cmd = new SqlCommand(sql + " " + string.Join(",", sql2.ToArray()), conn))
                {
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
        #endregion

        #region DataTable 转换
        public static List<Dictionary<string, object>> GetEntity(DataTable dt)
        {
            //返回json 需要引用json.net/Newtonsoft.Json 这两个其中一个dll
            string json = JsonConvert.SerializeObject(dt);

            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            //表示数据量大于0
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Columns.Count > 0)
                    {

                        Dictionary<string, object> row = new Dictionary<string, object>();
                        ///获取所有列名 item.ColumnsName
                        foreach (DataColumn item in dt.Columns)
                        {
                            row.Add(item.ColumnName, dt.Rows[i][item.ColumnName]);
                        }
                        rows.Add(row);
                    }
                }
            }
            return rows;
        }
        #endregion

        #region EF执行Sql语句返回DataSet
        public DataSet RunSql(string sql, params SqlParameter[] para)
        {
            DataSet ds = new DataSet();
            List<string> paraNames = new List<string>();
            for (int i = 0; i < para.Length; i++)
            {
                SqlParameter p = para[i];
                paraNames.Add(p.ParameterName);
            }
            using (var db = new BaseContext())
            {
                SqlConnection conn = db.Database.Connection as SqlConnection;
                using (SqlCommand cmd = new SqlCommand(sql + " " + string.Join(",", paraNames), conn))
                {
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                }
            }
            return ds;
        }
        #endregion

    }
}
